sql server 性能

In this article, we’re going through many of the tools we can use for monitoring SQL Server performance. SQL Server is chock-full of lots of good reports that allow a DBA to quickly spot whether there is any current performance bottleneck on the SQL Server. Many of these sit on top of DMVs but they give us a visually interactive way to look and work with the data. We’re going to start with SQL Server Performance Dashboard Reports.

在本文中，我们将介绍许多可用于监视SQL Server性能的工具。 SQL Server充满了很多好的报告，这些报告使DBA可以快速发现SQL Server上是否存在任何当前的性能瓶颈。 其中许多位于DMV之上，但它们为我们提供了一种可视化的交互方式来查看和处理数据。 我们将从SQL Server性能仪表板报告开始。

Let’s jump into SQL Server Management Studio (SSMS) and the first thing we’re going to take you through out-of-the-box dashboard reports of all levels. They can be found by right-clicking the SQL Server instance in Object Explorer, and from the context menu, you’ll find Reports > Standard Reports:

让我们进入SQL Server Management Studio（SSMS），首先我们要带您了解各个级别的现成仪表板报告。 可以通过在对象资源管理器中右键单击SQL Server实例来找到它们，然后从上下文菜单中找到“报告” >“ 标准报告” ：

All of the dashboard reports are useful, and we are not going to go through all of them as that would require a lot of time/words though feel free to check all of them whenever you get the chance. To show an example, choose the Server Dashboard from the context menu. This report gives us a lot of information about SQL Server instance current state including its configuration, version, services, and activity on it:

所有的仪表板报告都是有用的，我们将不进行所有的报告，因为这将需要大量的时间/单词，但是只要您有机会可以随时检查所有的报告。 要显示示例，请从上下文菜单中选择“ 服务器仪表板 ”。 该报告为我们提供了有关SQL Server实例当前状态的许多信息，包括其配置，版本，服务以及对其的活动：

Here we also have non-default configuration options. These are the ones that have been changed from the vanilla installation:

在这里，我们还有非默认配置选项。 这些是从原始安装更改的：

Also, at the database level, if we right-click on a database, and go into reports, we have all kind of disk usage reports, backup and restore events, top transactions, index statistics, etc.:

另外，在数据库级别，如果我们右键单击数据库并进入报告，我们将获得各种磁盘使用情况报告，备份和还原事件，主要事务，索引统计信息等：

So, all of these dashboard reports are great, they’re easy to consume and work with.

因此，所有这些仪表板报告都很棒，易于使用和使用。

Next, we’re going to look at the Activity Monitor which is a real-time monitor within SQL Server we can use to monitor everything from performance to I/O costs to expensive queries, etc. To launch Activity Monitor, right-click on the SQL Server instance in the Object Explorer and from the context menu choose Activity Monitor. You can also launch it from the Standard toolbar, by clicking the Activity Monitor icon:

接下来，我们将看一下活动监视器 ，它是SQL Server中的实时监视器，可用于监视从性能到I / O成本到昂贵查询等的所有内容。要启动活动监视器，请右键单击在对象资源管理器中选择SQL Server实例，然后从上下文菜单中选择“ 活动监视器”。 您也可以通过单击“ 活动监视器”图标从“标准”工具栏启动它：

Activity Monitor has always been one of the go-to tools if something suddenly goes wrong with SQL Server performance. The first thing we see, when we fire up Activity Monitor is the Overview pane. Furthermore, this tool has following expandable and collapsible panes: Resource Waits, Data File I/O, Recent Expensive Queries, and Active Expensive Queries:

如果SQL Server性能突然出问题，那么Activity Monitor一直是首选工具之一。 启动活动监视器时，我们看到的第一件事是“ 概述”窗格。 此外，此工具具有以下可扩展和可折叠窗格：资源等待，数据文件I / O，最近的昂贵查询和活动的昂贵查询：

Processes – give us the ability to look at currently running processes so we can manage them. Right-click brings up the context menu from where they can be terminated, traced in SQL Server Profiler (more about it later), viewed as an execution plan, and last but not least, the session details which pops up a dialog showing last T-SQL command batch:

流程 –使我们能够查看当前正在运行的流程，以便我们对其进行管理。 右键单击将弹出上下文菜单，可以在其中终止它们，在SQL Server Profiler中进行跟踪（稍后对其进行详细介绍），将其视为执行计划，最后但并非最不重要的是，会话详细信息会弹出一个对话框，显示最后一个-SQL命令批处理：

Resources Waits – shows information about waits for resources:

资源等待 –显示有关资源等待的信息：

Data File I/O – shows current data file I/O information that is happening at the file level:

数据文件I / O –显示在文件级别发生的当前数据文件I / O信息：

Recent/Active Expensive Queries – shows recent/active expensive queries that use many resources (memory, disk activity, network):

最近/活动的昂贵查询 –显示使用许多资源（内存，磁盘活动，网络）的最近/活动的昂贵查询：

This is a great starting point for finding what queries are causing problems, taking too many resources, etc. because once found, they can be viewed as the execution plan so that hot spots can be easily found:

这是查找导致问题的查询，占用过多资源等的好起点，因为一旦找到它们，就可以将它们视为执行计划，从而可以轻松找到热点：

Moving on, let’s check out top DMVs that every DBA should know. These are the DMVs that you should always have in your pocket. To get a quick list of all DMOs (DMVs and DMFs) just query the batch below:

继续，让我们检查每个DBA应该知道的顶级DMV。 这些是您应该始终放在口袋里的DMV。 要获取所有DMO（DMV和DMF）的快速列表，只需查询以下批次：

This will return all DMVs & DMFs in SQL Server. Look at the type column and note that “V” stands for a view, and “IF” for a function:

这将返回SQL Server中的所有DMV和DMF。 查看类型列，注意“ V”代表视图，“ IF”代表功能：

The above query returned 243 DMOs on a system. Here’s the list of the most useful ones:

上面的查询返回了系统上的243个DMO。 以下是最有用的列表：

Execution

执行

sys.dm_exec_connections = Established connection sys.dm_exec_sessions = Authenticated sessions sys.dm_exec_requests = Current requests

sys.dm_exec_connections =建立的连接 sys.dm_exec_sessions =经过身份验证的会话 sys.dm_exec_requests =当前请求

Execution (query related)

执行（与查询相关）

sys.dm_exec_cached_plans = Cached execution plans sys.dm_exec_query_plan = Show plan for a given cached plan_handle sys.dm_exec_query_stats = Query performance stats sys.dm_exec_sql_text = SQL text given a sql_handle

sys.dm_exec_cached_plans =缓存的执行计划 sys.dm_exec_query_plan =显示给定缓存的plan_handle的计划 sys.dm_exec_query_stats =查询性能统计 sys.dm_exec_sql_text =给定sql_handleSQL文本

Index

指数

sys.dm_db_index_physical_stats = Index size and fragmentation sys.dm_db_index_usage_stats = index usage via the query optimizer sys.dm_db_missing_index_details = Discover missing indexes

sys.dm_db_index_physical_stats =索引大小和碎片 sys.dm_db_index_usage_stats =通过查询优化器使用索引 sys.dm_db_missing_index_details =发现丢失的索引

OS

操作系统

sys.dm_os_performance_counters = List of all SQL Server performance counters and values sys.dm_os_schedulers = Detect CPU pressure sys.dm_os_waiting_tasks = Tasks waiting on resources sys.dm_os_wait_stats = All waits types and stats

sys.dm_os_performance_counters =所有SQL Server性能计数器和值的列表 sys.dm_os_schedulers =检测CPU压力 sys.dm_os_waiting_tasks =等待资源的任务 sys.dm_os_wait_stats =所有等待类型和状态

I/O

输入/输出

sys.dm_io_virtual_file_stats = I/O stats for data and log files sys.dm_io_pending_io_requests = Pending I/O requests

sys.dm_io_virtual_file_stats =数据和日志文件的I / O状态 sys.dm_io_pending_io_requests =待处理的I / O请求

CLR

CLR

sys.dm_clr_loaded_assemblies = Loaded assemblies sys.dm_clr_tasks = CLR related tasks

sys.dm_clr_loaded_assemblies =加载的程序集 sys.dm_clr_tasks = CLR相关任务

The books online (docs.microsoft.com) have a great overview pages for all the DMOs. Feel free to copy any name from the result grid, paste it into the browser and search for it. Most likely the very first article at the top will be from MS docs showing the overview and usage of T-SQL.

联机丛书（ docs.microsoft.com ）具有适用于所有DMO的出色概述页面。 随意从结果网格中复制任何名称，将其粘贴到浏览器中并进行搜索。 最上面的第一篇文章很可能来自MS docs，其中显示了T-SQL的概述和用法。

At the very end, let’s take a peek at the SQL Server Profiler to create and replay a trace. That’s extremely useful when troubleshooting problems whether they’re from an application or internally within the database. Creating a trace allows us to capture a workload. And if that workload is the one causing problems than having the ability to reproduce that problem will make it much easier to verify if we actually fixed it when resolving the issue.

最后，让我们看一下SQL Server Profiler来创建和重放跟踪。 当对问题进行故障排除时，这是非常有用的，无论这些问题是来自应用程序还是来自数据库内部。 创建跟踪可以使我们捕获工作负载。 并且，如果该工作负载是导致问题的原因，那么比具有重现该问题的能力将使验证我们在解决该问题时是否确实解决该问题更加容易。

Unfortunately, Microsoft announced deprecation of SQL Server Profiler, but this feature is still available in SQL Server 2016 and will be removed in the future versions.

不幸的是，Microsoft宣布弃用SQL Server Profiler，但此功能在SQL Server 2016中仍然可用，并且在以后的版本中将删除。

So, let’s move on to the SQL Server Profiler and see how it works. To start SQL Server Profiler, hit the Windows Start icon or press the Windows key and enter “SQL Server Profiler 17”:

因此，让我们继续进行到SQL Server Profiler，看看它是如何工作的。 要启动SQL Server Profiler，请单击Windows“开始”图标或按Windows键并输入“ SQL Server Profiler 17”：

Remember, the SQL Server Profiler can be initialized from Activity Monitor as well, just click the Processes pane, right-click the process that you want to profile, and then from the right-click context menu choose the Trace Process in SQL Server Profiler option.

记住，SQL Server Profiler也可以从活动监视器中初始化，只需单击“进程”窗格，右键单击要配置的进程，然后从右键单击上下文菜单中选择“在SQL Server Profiler中跟踪进程”选项。

So, once this fires up, click the very first button that says, “New Trace”. This will bring up the connection dialog to your instance:

因此，一旦启动，请单击第一个按钮“ New Trace”。 这将打开连接对话框到您的实例：

After the connection is established to the instance you want to profile and create traces against, the Trace Properties window will appear. Give the trace a name in the Trace name box. And since we want to replay this trace, select a built-in trace template because there are a couple of requirements with replays, certain events and fields need to be captured and the easy way to do this is to choose the TSQL_Replay template from the Use the template list:

与您要分析并创建跟踪的实例建立连接后，将显示“ 跟踪属性”窗口。 在“ 跟踪名称”框中为跟踪命名 。 并且由于我们要重播此跟踪，因此选择一个内置的跟踪模板，因为重播有几个要求，需要捕获某些事件和字段，而执行此操作的简单方法是从“ 使用”中选择TSQL_Replay模板。 模板列表：

For now, save this as a file by checking the Save to file option and then change the tab to Events Selection. Since we choose a template, it’s going to preselect a lot of events. From here, you can play with the options, show all events/columns, add/remove certain events, etc.:

现在，通过选中“ 保存到文件”选项将其另存为文件 ，然后将选项卡更改为“ 事件选择” 。 由于我们选择了模板，因此它将预先选择很多事件。 在这里，您可以使用这些选项，显示所有事件/列，添加/删除某些事件等：

In this case, let’s click the Column Filters button to set a filter on a database name. We only want to trap events that hit the AdventureWorks2014 database. This is a great way to trim out all the extra noise that will be coming in a real-world scenario:

在这种情况下，让我们单击“ 列过滤器”按钮以在数据库名称上设置过滤器。 我们只想捕获击中AdventureWorks2014数据库的事件。 这是消除实际场景中所有多余噪声的好方法：

Once all this is set up, click OK in the Edit Filter dialog to save the setting and close it. Back in the initial windows, hit the Run button to start the trace. So, this trace is running now, monitoring everything that is happening in the AdventureWorks2014 database:

全部设置完成后，在“ 编辑过滤器”对话框中单击“ 确定 ”以保存设置并关闭它。 返回初始窗口，单击“ 运行”按钮以开始跟踪。 因此，此跟踪现在正在运行，监视AdventureWorks2014数据库中发生的一切：

Switch over SSMS and execute some queries or just let it run in the background for a while. Various events will be captured even if no user queries are hitting the database:

切换SSMS并执行一些查询，或者只是让它在后台运行一段时间。 即使没有用户查询访问数据库，也会捕获各种事件：

After sitting there for a while, stop the trace by clicking the red stop button as shown bellow:

坐在那里一段时间后，单击红色的停止按钮，停止跟踪，如下所示：

Now what we got everything captured inside that previously saved file, we can for example run this replay on a different instance. Why? Because it can be extremely useful when you’re having a problem in production. It’s very unlikely that any big issues can be fixed directly on a production server. In this use case scenario, the best approach would be to copy production to test, take that trace that was captured in production, and then we can do anything we want on that test database in terms of troubleshooting and fixing the problem.

现在，我们将所有捕获的内容保存在先前保存的文件中，例如，可以在其他实例上运行此重放。 为什么？ 因为当您在生产中遇到问题时，它非常有用。 任何大问题都不可能直接在生产服务器上解决。 在此用例场景中，最好的方法是将生产复制到测试中，获取在生产中捕获的跟踪，然后就故障排除和解决问题而言，我们可以在该测试数据库上执行任何操作。

To do this, open up the trace in SQL Server Profiler:

为此，请在SQL Server Profiler中打开跟踪：

Now, go to Replay and choose Start:

现在，转到“ 重放”并选择“ 开始” ：

This will, once again, bring up the SQL Server connection dialog to your instance:

这将再次调出SQL Server连接对话框到您的实例：

Which will then take us to the Replay Configuration window in which we can change the replay server if we need to for the purpose mentioned earlier. It’s pretty much straightforward from this point. Just hit OK to replay the trace:

然后将带我们进入“重播配置”窗口，如果我们需要出于前面提到的目的，可以在其中更改重播服务器。 从这一点来看，这非常简单。 只需单击确定以重播跟踪：

The first row will always contain all the Replay Setting Event. Then anytime there is an event, it will fire off that event. The result set will only indicate that it’s done:

第一行将始终包含所有重播设置事件。 然后，只要有事件发生，就会触发该事件。 结果集将仅指示已完成：

If we scroll to the right, any result set will have the profiler measured duration – how long it took to run an event:

如果我们向右滚动，则任何结果集都将具有探查器测量的持续时间-运行事件花费了多长时间：

At the very end, you’ll find the Replay Statistics Event which shows the total events, provider/internal errors that occurred, hit ratio stats, and the total replay time:

最后，您将找到“ 重播统计信息事件” ，其中显示了总事件，发生的提供者/内部错误，命中率统计信息以及总重播时间：

Again, this is good for scenarios when there’s a problem with SQL Server performance and you want to recreate the problem. First of all, capture the problem which will then give us the workflow inside of a trace that we can do whatever it’s needed on SQL Server to troubleshoot and fix the SQL Server performance issue and then finally we can replay that trace, reproduce that issue to see if we have successfully fixed it.

同样，这对于当SQL Server性能存在问题并且您想重新创建问题时的情况非常有用。 首先，捕获问题，然后将问题带给我们跟踪的工作流，我们可以执行SQL Server上需要执行的任何操作来解决和修复SQL Server性能问题，然后最后可以重播该跟踪，将该问题重现为看看我们是否已成功修复它。

In this article, we covered four tools for monitoring SQL Server performance. Hopefully, you’ll now be able to collect performance and system information using Database Reports for interactive visual insight on what SQL Server is doing. Then we looked at a real-time tool for monitoring SQL Server performance called Activity Monitor. We also looked at how to use DMVs and at the very end, we learned how to use SQL Server Profiler.

在本文中，我们介绍了用于监视SQL Server性能的四个工具。 希望您现在可以使用数据库报表收集性能和系统信息，以交互方式直观了解SQL Server的工作方式。 然后，我们研究了一种用于监视SQL Server性能的实时工具，称为“活动监视器”。 我们还研究了如何使用DMV，最后，我们学习了如何使用SQL Server Profiler。

翻译自: https://www.sqlshack.com/how-to-collect-performance-and-system-information-in-sql-server/

sql server 性能